package com.kkm.dao.Imp;

import com.kkm.dao.MusicDao;
import com.kkm.pojo.Song;
import com.kkm.util.JdbcUtils_Druid;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class MusicDaoImp implements MusicDao{
    private JdbcTemplate template=new JdbcTemplate(JdbcUtils_Druid.getDataSource());
    @Override
    public List<Song> showAllMusic(int index, int rows) {
        String sql="select * from song  limit ?,?";
       // String sql="select song.id,song.name,song.introduction,singer.name from song left join (singer on song.singer_id=singer.id) order by song.id limit ?,?";
        return template.query(sql,new BeanPropertyRowMapper<>(Song.class),index,rows);
    }

    @Override
    public int checkMusicCount() {
        String sql="select count(*) from song";
        return template.queryForObject(sql,int.class);
    }

    @Override
    public int checkMusicNum(Map<String, String[]> condition) {
        String sql="select count(*) from song where 1=1 ";
        //解析map  1.对sql拼接
        StringBuffer sb=new StringBuffer(sql);
        //2.获取键值，name address，email
        Set<String> keySet=condition.keySet();
        //3.定义参数集合
        List<Object> params=new ArrayList<Object>();
        //遍历看那个不为空拼接到sql语句中
        //key：name，address，email
        for(String key:keySet){
            //排除分页条件参数
            if ("currentPage".equals(key)||"rows".equals(key)||"method".equals(key)){
                continue;
            }
                String value=condition.get(key)[0];//获取值
                if(key.equals("singer")){
                    value=condition.get(key)[0];//获取值
                    if(value!=null||"".equals(value)){
                        //有值
                        sb.append(" and name like ? ");
                        params.add("%%"+value+"%");
                    }
                }else {
                    if(value!=null||"".equals(value)){
                        //有值
                        sb.append(" and "+key+" like ? ");
                        params.add("%%"+value+"%");
                    }
                }
        }
        System.out.println(sb.toString());
        return template.queryForObject(sb.toString(),int.class,params.toArray());
    }

    @Override
    public List<Song> checkMusic(Map<String, String[]> condition, int index, int rows) {
        String sql="select * from song where 1=1 ";
        //解析map  1.对sql拼接
        StringBuffer sb=new StringBuffer(sql);
        //2.获取键值，name address，email
        Set<String> keySet=condition.keySet();
        //3.定义参数集合
        List<Object> params=new ArrayList<Object>();
        //遍历看那个不为空拼接到sql语句中
        //key：name，address，email
        for(String key:keySet){
            //排除分页条件参数
            if ("currentPage".equals(key)||"rows".equals(key)||"method".equals(key)){
                continue;
            }
            String value=condition.get(key)[0];//获取值
            if(key.equals("singer")){
                value=condition.get(key)[0];//获取值
                if(value!=null||"".equals(value)){
                    //有值
                    sb.append(" and name like ? ");
                    params.add("%%"+value+"%");
                }
            }else {
                if(value!=null||"".equals(value)){
                    //有值
                    sb.append(" and "+key+" like ? ");
                    params.add("%%"+value+"%");
                }
            }
        }
        sb.append(" limit ?,? ");
        params.add(index);
        params.add(rows);
        sql=sb.toString();
        System.out.println(sql+"11");
        return template.query(sql,new BeanPropertyRowMapper<Song>(Song.class),params.toArray());
    }

    @Override
    public void deleteMusic(int sid) {
        String sql="delete from song where id=?";
        template.update(sql,sid);
    }

    @Override
    public void modifyMusic(Map<String, String[]> condition, int sid) {
        String sql="update song set ";
        //解析map  1.对sql拼接
        StringBuffer sb=new StringBuffer(sql);
        //2.获取键值，name address，email
        Set<String> keySet=condition.keySet();
        //3.定义参数集合
        List<Object> params=new ArrayList<Object>();
        //遍历看那个不为空拼接到sql语句中
        //key：name，address，email
        for(String key:keySet){
            //排除分页条件参数
            if ("currentPage".equals(key)||"rows".equals(key)||"method".equals(key)){
                continue;
            }
            String value=condition.get(key)[0];//获取值
           if(value!=null||"".equals(value)){
               if(key.equals("singer_id")){
                   sb.append(" "+key+" = ? ");
                   params.add(value);
               }else if (key.equals("sid")){

               }else {
                   //有值
                   sb.append(", "+key+" = ? ");
                   params.add(value);
               }
           }
        }
        sb.append(" where id=? ");
        params.add(sid);
        sql=sb.toString();
        System.out.println("修改的sql："+sql);
        template.update(sql,params.toArray());
    }

    @Override
    public Song checkMusicById(int sid) {
        String sql="select * from song where id=?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<Song>(Song.class),sid);
    }

    @Override
    public int addMusic(Song song) {
        String[] split = song.getName().split("-");
        String singer=split[0];
        String sql="insert into song(name,pic,lyric,url,create_time,update_time) values (?,?,?,?,'2019-06-03 01:38:40','2019-06-03 01:38:40')";
        try{
            int update = template.update(sql,song.getName(), song.getPic(), song.getLyric(), song.getUrl());
            return  update;
        }catch (Exception e){
            return  0;
        }



    }
}
